package com.weixinadmin.dao;
import java.sql.Connection;   
import java.sql.PreparedStatement;   
import java.sql.ResultSet;   
import java.sql.SQLException;   
import java.sql.Timestamp;
import java.util.Iterator;
import java.util.List;   
  





import javax.sql.DataSource;   

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;   
import org.springframework.jdbc.core.PreparedStatementCreator;   
import org.springframework.jdbc.core.RowMapper;   
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;   
import org.springframework.jdbc.support.KeyHolder;   

import com.weixinadmin.dao.CustomerGroupSql.custmergroupRowMapper;
import com.weixindamin.exception.wxexception;

public class CustomerSql {
		
	 private static Logger logger = Logger.getLogger(CustomerSql.class);//log4j
		
	  private JdbcTemplate jdbcTemplate;   
     //private NamedParameterJdbcTemplate namedParameterJdbcTemplate;   
	  //private SimpleJdbcTemplate simpleJdbcTemplate;   
	  private Customerdao custmer;
	  private final static String table = "customer" ;
		 
	class custmerRowMapper implements RowMapper {  
		        
		public Object mapRow(ResultSet rs, int index) throws SQLException {  
		Customerdao custmer = new Customerdao();  		      
		custmer.setopenid(rs.getString("openid"));   
		custmer.setcity(rs.getString("city"));   
		custmer.setcountry(rs.getString("country"));   
		custmer.setheadimgurl(rs.getString("headimgurl"));   
		custmer.setlanguage(rs.getString("language"));   
		custmer.setnickname(rs.getString("nickname"));   
		custmer.setprovince(rs.getString("province"));
		custmer.setsex(rs.getInt("sex"));
		custmer.setgroupid(rs.getInt("groupid"));		
		custmer.setsubscribe(rs.getInt("subscribe"));
		custmer.setsubscribetime(rs.getTimestamp("subscribetime"));
		custmer.setrejecttime(rs.getTimestamp("rejecttime"));
		custmer.setgroupid(rs.getInt("groupid"));   
		return custmer;         	      
		}         
		      
	 }  
	
	 public CustomerSql(){
		 this.jdbcTemplate = new JdbcManager().getjdbctemplate();
	 } 	  
	  
	  public List<Customerdao> getcustmer() {

		  // TODO Auto-generated method stub
		  if(null==jdbcTemplate){
			  logger.info("jdbcTemplate is null");
		  }
         return jdbcTemplate .query( "select * from "+table ,
       		  new custmerRowMapper());

	  } 
	  
	  public Customerdao getcustmer(int id) {

         // TODO Auto-generated method stub
		  if(null==jdbcTemplate){
			  logger.info("jdbcTemplate is null");
		  }
         return (Customerdao) jdbcTemplate.queryForObject(
       		  "select * from "+table+" where id=?" , new Object[] { id }, 
                  new int [] { java.sql.Types. INTEGER }, new custmerRowMapper());

	  }
	  
	  public void updateinfo(Customerdao custmer) throws wxexception{

		  	/*
		  	if(null==jdbcTemplate){
				  logger.error("jdbcTemplate is null");
				  return ;
			}
			*/
			String custmerinfosql = "openid =?, nickname=?, language=?, city=?, province=?, country=?, headimgurl=?, subscribe=?, sex=?, subscribetime=?";
			String sql = "INSERT "+table+" SET " +custmerinfosql+ "ON DUPLICATE KEY UPDATE "+custmerinfosql;
			try { 
				jdbcTemplate.update(sql,
						new Object[]{
						custmer.getopenid(),custmer.getnickname(),custmer.getlanguage(),
						custmer.getcity(),custmer.getprovince(),custmer.getcountry(),
						custmer.getheadimgurl(),custmer.getsubscribe(),custmer.getsex(),custmer.getsubscribetime(),
						custmer.getopenid(),custmer.getnickname(),custmer.getlanguage(),
						custmer.getcity(),custmer.getprovince(),custmer.getcountry(),
						custmer.getheadimgurl(),custmer.getsubscribe(),custmer.getsex(),custmer.getsubscribetime()
						});
			} catch (Exception e) {  
				  logger.error("sql Exception: "+ sql);
				  e.printStackTrace();   
				  throw new wxexception(new wxexception().sqlerr,sql);
			}  
	  }
	  
	  public void updategroupid(String openid, int groupid) throws wxexception{
		  	/*
		  	if(null==jdbcTemplate){
				  logger.info("jdbcTemplate is null");
				  return ;
			}	
	
		  	*/String sql = "UPDATE "+table+" SET groupid=? WHERE openid = '" + openid + "'";
			
			try { 
				//UPDATE nlog SET " +mcsql+ " WHERE nlog.cmmmsgid = ? 
				jdbcTemplate.update(sql,new Object[]{groupid});
			} catch (Exception e) {  
				 logger.error("sql Exception: "+ sql);
				  e.printStackTrace();   
				  throw new wxexception(new wxexception().sqlerr,sql);
			}  
	  }
	  
	  public void updaterejecttime(String openid, Timestamp rejecttime) throws wxexception{
		  /*
		  	if(null==jdbcTemplate){
				  logger.error("jdbcTemplate is null");
				  return ;
			}
			*/
		  	String sql = "UPDATE "+table+" SET rejecttime=? WHERE openid =  '" + openid + "'";			
			try { 
				//UPDATE nlog SET " +mcsql+ " WHERE nlog.cmmmsgid = ? 
				jdbcTemplate.update(sql,new Object[]{rejecttime});
			} catch (Exception e) {  
				 logger.error("sql Exception: "+ sql);
				  e.printStackTrace();   
				  throw new wxexception(new wxexception().sqlerr,sql);
			}   
	  }
	  
	  public void delall() throws wxexception{	
		  /*
		  if(null==jdbcTemplate){
			  logger.error("jdbcTemplate is null");
			  return ;
		  }
		  */
		  String sql = "delete from "+table;
		  try { 
			  jdbcTemplate.update(sql,
					  new Object[]{});
		  }	catch (Exception e) {  
				 logger.error("sql Exception: "+ sql);
				  e.printStackTrace();   
				  throw new wxexception(new wxexception().sqlerr,sql);
			} 
	  
	  }
	  public void del(String openid) throws wxexception{
		  /*
		  if(null==jdbcTemplate){
			  logger.info("jdbcTemplate is null");
			  return ;
		  }
		  */
		  String sql = "delete from "+table+" WHERE openid =  '" + openid + "'";
		  try { 
			  jdbcTemplate.update( sql ,
					  new Object[]{});
		  }	catch(Exception e) {  
				 logger.error("sql Exception: "+ sql);
				  e.printStackTrace();   
				  throw new wxexception(new wxexception().sqlerr,sql);
			} 
	  
	  }
	 	
	  
	  public static void main(String[] args) throws wxexception  {
		  
		  Customerdao c = new Customerdao();
		  c.setcity("丰台");
		  c.setcountry("中国");
		  c.setheadimgurl("http://hello");
		  //c.setgroupid(groupid);
		  c.setlanguage("cn_zh");
		  c.setnickname("mm");
		  c.setopenid("qqqqcdews_ploikju");
		  c.setprovince("北京");
		  c.setsex(1);
		  c.setsubscribe(1);
		  c.setsubscribetime(new Timestamp(System.currentTimeMillis()));
		  
		  CustomerSql cs = new CustomerSql();
		  
		  cs.updateinfo(c);
		  cs.updategroupid(c.getopenid(), 1);
		  cs.updaterejecttime(c.getopenid(), new Timestamp(System.currentTimeMillis()) );
		  
		  System.out.println("in: "+ c.toString());
		  List<Customerdao> css =  cs.getcustmer();
		  
		  Iterator<Customerdao> itr = css.iterator();
	        while (itr.hasNext()) {
	        	c = itr.next();
	        	System.out.println("out: " +  c.toString());
	    		 
	        }
        
 		} 
		 	
	 
	
}
